AssetWise System Management Console Help

Configuring the FTR Service

Full text retrieval (FTR) allows the text within files in AssetWise to be indexed and stored in a database. You can then search for files in AssetWise based on that text.

To configure FTR you must:

  1. Configure your database server for FTR.

    The database product you use for FTR depends on which database you are using for the AssetWise datasource / community. If your community uses SQL Server, then FTR will use SQL Server; if your community uses Oracle, then FTR will use Oracle.

    FTR with SQL Server requires a SQL Server database with FILESTREAM storage enabled. You also need to install the iFilters for the file types you want to index.

    FTR with Oracle requires configuration of Oracle Text components within Oracle.

    See the "Database Setup" section of the AssetWise Implementation Guide for details.

    Note: AssetWise does not support FTR when using Microsoft Azure SQL Database.
  2. Install FTR Service and Storage Service.

    FTR Service and Storage Service are both options of the AssetWise Server installer. Both services are installed by default and are listed as FTR Service and Storage Service, respectively, in the installer.

    You can use the FTR Service and Storage Service running on your main AssetWise Server (AssetWise Information Integrity Server) computer. You can also use the AssetWise Server installer to install FTR Service and Storage Service on the same computer as your database server, or even on a standalone computer.

    Depending on the amount of files that need to be indexed, you may want to consider installing another instance of FTR Service on a separate computer. For example, if you are configuring FTR for the first time for an existing community that contains a large number of files, you may want to install a second instance of FTR Service on another computer to distribute the work and shorten the time it takes to index all the files. However, once all the existing files in the community are indexed, a single instance of FTR Service may suffice going forward, because any new files will be automatically submitted for indexing shortly after they are added to the community.

  3. If you plan to index DWG files, the FTR Service and ProjectWise InterPlot Server must be installed on the same machine (check for the latest supported Windows Server for ProjectWise InterPlot Server).

    By default, AssetWise uses ProjectWise InterPlot Server to create a PDF rendition of the DWG file to be indexed. Once the PDF exists, AssetWise indexes the PDF instead of the DWG file.

    Note: If you have a DWG iFilter you would like to use instead of ProjectWise InterPlot Server, see "Database Setup > Setting Up SQL Server for AssetWise > Configuring SQL Server for FTR > Installing iFilters" in the AssetWise Implementation Guide.
  4. Enable both the FTR Service and the Storage Service for your community in AssetWise System Management Console.

    The steps for configuring FTR Service are slightly different depending on whether you are using SQL Server-based FTR or Oracle-based FTR.

    Enabling the Storage Service is already documented elsewhere in this guide; see To Enable the Storage Service for Your Community .

    Note: The user account under which the Storage Service runs must have permission to access the FTR repository folder on the database server.
  5. Select which classes of documents you want indexed.

    In a new datasource and community, there are no document classes defined yet. You create document classes in AssetWise Director. Document classes must exist in order to add files to the community, but also, they must exist in order to configure FTR because part of the configuration involves selecting which classes you want to include in FTR indexing. AssetWise will only index text from files whose documents belong to the classes you select for inclusion in FTR indexing. Once you create document classes in AssetWise Director, they will be selectable in AssetWise System Management Console for inclusion in FTR indexing.

    Tip: See the AssetWise Director documentation for details.
Parent topic: Configuring AssetWise Services

To Manually Create an FTR Repository

When files in your community are submitted for indexing, they are copied from their original repository to a dedicated FTR repository where they can be processed. Creating an FTR repository is basically the same as creating a regular repository, except that you must turn on the option stating that the repository is Used for Full Text Retrieval. That option is what makes it an FTR repository. There can only be one FTR repository per community. The path to the FTR repository must be accessible with read permissions as a UNC drive from the database server.

Important: Manual creation of the FTR repository (using either dialog below) is required for Oracle-based communities. Manual creation of the FTR repository is optional for SQL Server-based communities, because for those communities you can let AssetWise create the FTR repository for you when enabling the FTR Service in AssetWise System Management Console.

When creating a repository using the New Repository Properties dialog, turn on Used for Full Text Retrieval

When creating a repository using the Storage Wizard, turn on Repository will be used for Full Text Retrieval

To Enable the FTR Service for Your Community - SQL Server

This task is for communities that are using a SQL Server database.

  1. In the Explorer pane, expand Servers > your_server.
  2. Select the FTR node.
  3. In the Communities Served list, check the check box next to your community.
  4. Log in to the community if prompted.

    The FTR Database Settings dialog opens. Here you select which database will contain the FTR FileTable. The database you select must be a SQL Server database with FILESTREAM storage enabled.

  5. In the FTR Database Settings dialog:
    1. Turn on Use current eB Database if you want to store the FTR FileTable inside the community's database, or turn off Use current eB Database to select another SQL Server database in which to store the FTR FileTable, then do the following:
      1. In the FTR Database Server field, enter the name of the computer where SQL Server is installed.

        If your SQL Server installation has named instances, enter the name of the computer, followed by a backslash, followed by the SQL Server instance name you want to connect to. For example: computername\SQLServerinstancename

      2. Specify the account you want to log in to the specified SQL Server instance with, either by entering the Username and Password of a SQL Server account, or by turning on Use Windows Authentication to log in to SQL Server using your current Windows account.
      3. In the Database list, select the database in which you want to store the FTR FileTable.
    2. Click OK.

    Another Properties dialog opens. Here you select whether to create a new or select an existing FileTable in the selected database. In most cases you will create a new FileTable. If you select an existing FileTable, it must be empty and not used for any other community.

  6. In the Properties dialog:
    1. Select Create a new FileTable if the selected database does not already have one, or if you want to use an existing (and empty) FileTable in the selected database, select Use existing File Table and select the FileTable from the list.
    2. Turn on Create and configure FTR file repository.
    3. Click OK.

      This creates a storage device and an FTR repository in AssetWise System Management Console, along with the physical folder for the repository on the SQL Server computer at the location supplied in the FTR Repository Location field. While you can turn off Create and configure FTR file repository and create an FTR repository manually, allowing the FTR repository to be created automatically ensures that the proper syntax is used for the UNC path.

  7. Make sure that the user account under which the Storage Service is running has access to the new FTR repository on the SQL Server computer. It is the job of the Storage Service to copy the files submitted for indexing over to the FTR repository on the SQL Server computer. Therefore, make sure the Storage Service has access to that folder.
Note: If you turned off Create and configure FTR file repository, you will have to manually create the storage device and the FTR repository in AssetWise System Management Console. See To Create a Storage Device and To Manually Create an FTR Repository . When creating the device, make sure you use a UNC path using this syntax. When creating an FTR repository for a SQL Server community, the syntax is \\SQLServercomputername\SQLServerInstanceName\DatabaseDirectory

To Enable the FTR Service for Your Community - Oracle

This task assumes that you have already created a storage device and an FTR repository under that device.

  1. In the Explorer pane, expand Servers > your_server.
  2. Select the FTR node.
  3. In the Communities Served list, check the check box next to your community.
  4. Log in to the community if prompted.

    The Properties dialog opens.

  5. Enter the path to the FTR repository, relative to the database server.
  6. Click OK.

To Select the Classes of Documents Whose Files You Want to Index

  1. In the Explorer pane, expand Servers > your_server > Communities.
  2. Expand your community and select the FTR node.
    Tip: This is different from the main FTR node where you enabled the FTR Service for the community.
  3. In the content pane, check the check box next to each class of documents whose files you want to be indexed. If you do not want a particular document class's files to be indexed, keep that class's check box turned off.

    If there are no files in your community yet, then you are done with the FTR configuration. Any new file that gets added to the community and that is associated with one of the selected document classes will be automatically submitted to the queue for FTR indexing.

To Synchronize Files with Selected Classes

As your community evolves, you may create new classes of documents and decide that you want to include them in FTR indexing. New classes are always turned off by default in the FTR node within your community. Or, you may decide that you no longer want files that are associated to a particular class of documents to be indexed. In either case, you will return to the FTR node within your community in AssetWise System Management Console, turn on or off the necessary classes, then synchronize the existing files with the selected classes. The following steps assume you are making FTR changes in an existing community where files have already been indexed.
  1. Expand your community and select the FTR node.
  2. In the content pane, do the following:

    Check the check box next to any new class of documents whose files want to be indexed.

    Uncheck the check box next to any previously selected class of documents whose files you no longer want to be indexed.

  3. Click the Synchronize Files with Selected Classes icon.
  4. Click Yes when prompted to save your changes.
  5. Click OK on the confirmation dialog.

    If you turned off any document classes, then upon synchronization, any existing files in the community that are associated to those classes and that were previously indexed are removed from the FTR repository, and the text from those files are removed from the FTR catalog.

    If you turned on any document classes, then upon synchronization, any existing files in the community that are associated to the newly selected document classes will be submitted to the queue for FTR indexing. Also, from this point forward, any file that is added to the community and that is associated to one of the selected document classes will be automatically submitted to the queue for FTR indexing.

To Check the FTR Queue

You can check the FTR queue to make sure the files submitted for indexing were processed.
  1. In the Explorer pane, expand Servers > your_server > Communities > your_community.
  2. Expand the Queues node within your community and select the Ftr folder.

Default Search Settings (Datasource - Search tab)

To see or change your default search settings for a datasource, select your datasource in the Explorer pane, then select the Search tab in the content pane. The settings on this tab are database dependent and are disabled if not applicable.

A number of new search settings have been added to address issues relating to case-sensitivity in Oracle databases.

SettingDescription
Force text to UPPERCASE when entered This setting forces all text entered by users into uppercase, allowing the database engine to execute very effective string comparisons since case does not then have to be taken into account.

All new text entered will be in uppercase.

The case of existing text is not changed.

Note: This option is typically only used on Oracle-based systems, since the database itself tends to be case-sensitive and performing case-insensitive searches therefore requires additional processing.
Enable Linguistic string sorting This setting enables Linguistic String Sorting by setting the NLS_COMP=LINGUISTIC option. This allows case-insensitive searches when the database stores data as case-sensitive.
Warning: There are known problems using this setting including:
  1. Table indexes being ignored and replaced with full table scans when certain functions or comparison operators (LIKE) are used which can severely degrade the performance.
  2. Incorrect results being returned when certain criteria are used.
  3. A bug list is displayed by clicking on the warning symbol.
Maximum Inner Rows A search in AssetWise consists of four distinct phases with the results of the first feeding into the second, and so on.
  1. The first phase does two things:
    • It creates the joins to the required tables, applies the user-defined criteria and selects the list of user specified columns.
    • It does the required permission lookup for each row/column that would need to be filtered. These values are added to the list of columns.
  2. The second phase does two things:
    • It sorts the user-defined select list according to the [user defined] sort order.
    • It groups all ‘duplicate’ rows. (Although these row are not true duplicates they could appear that way when only a subset of columns gets returned)
  3. The third phase does two things:
    • Removes all records that the user has no permission to see.
    • Clears out all columns (per row) that the user has no permission to see.
  4. The fourth phase does the paging (for example, returns rows 'StartAt' to 'StartAt + PageSize')

A search with weak predicates could yield a large resultset. Such a resultset is expensive on both processing [permission filters] and storage [temporary sorting area]. So it is important to keep the set small. To ensure that there is some control over that, the administrator can set the Maximum Inner Rows setting to around 10,000, for example.

A search can, as an option, perform a count. This count is across all rows that matches the user defined criteria. This count is only there as an estimate as it does not take any permission into consideration; neither does it get limited by the Maximum Inner Rows setting.

The problem with an accurate count is that it could be used to infer a value that the user may not see. For this reason we change the value of the estimate count to the actual RowCount whenever the rows returned is less than that of the PageSize but only if the estimate is smaller than the Maximum Inner Rows. The user interface can therefore use the EstimateHitCount to warn the user when the Maximum Inner Rows where exceeded.

Enable UPPER function based indexes This setting creates function-based indexes on all text-based columns to allow string comparisons using the UPPER syntax without the usual performance hit associated with the syntax.

A typical example of an UPPER comparison using PL/SQL would be: 

...WHERE table.column LIKE UPPER('value%')

Regardless of the casing of value all results would be returned but this could have a severe performance penalty especially on tables with many rows.

The UPPER function based indexes allows the comparisons in an efficient manner. To make use of the functions, ensure the column being compared is also converted to uppercase using the UPPER function: 

...WHERE UPPER(table.column) LIKE UPPER('value%')
Note: The syntax should be used whether eQL reports or PL/SQL queries are written. All reports, searches or queries need to use the correct syntax in order to make use of the function-based indexes.
Enable Full Text Search indexes This setting creates Full Text Retrieval indexes on all text-based columns to allow FTR-based searches which may result in better performance and allow more natural words to be used in the criteria, since the results will typically include similar words instead of the typically rigid comparisons without this functionality.

FTR queries using the eQL language may be performed using the CONTAINS or FREETEXT comparison operator: 

...WHERE table.column CONTAINS 'value'

To perform similar queries using Transact-SQL, refer to the SQL Server documentation for details on the CONTAINS and FREETEXT predicates.

Notes:

FTR functionality must be installed and enabled on the database in SQL Server Management Studio.

Some columns may not allow FTR indexes to be created against them. Please refer to the SQL Server documentation.

All reports, searches or queries need to use the correct syntax in order to make use of the function-based indexes.

When this option is disabled, all existing FTR column indexes will be removed and all queries, searches or reports that make use of the FTR syntax will fail to execute.

The indexes do require some additional space, and the indexing catalog must be maintained by running a scheduled job at regular intervals. Database administrators can easily modify how the catalogs are kept updated by modifying the Full-Text indexes on the tables from within the SQL Server Management Studio.